﻿
CREATE PROCEDURE [dbo].[proc_Finance_AccountsRecivableAnalysis_Company]
	@CompanyId varchar(1000)
AS
BEGIN
	IF Len(@CompanyId)=0 return
	declare @sql varchar(max)='
		declare @CurrentDate datetime=dateadd(MONTH,-1,Getdate())
		declare @OneMonth varchar(10),@ThreeMonth varchar(10),@SixMonth varchar(10),@OneYear varchar(10),@TwoYear varchar(10)
		Set @OneMonth=CONVERT(varchar(10),dateadd(MONTH,-1,@CurrentDate),120)
		Set @ThreeMonth=CONVERT(varchar(10),dateadd(MONTH,-4,@CurrentDate),120)
		Set @SixMonth=CONVERT(varchar(10),dateadd(MONTH,-7,@CurrentDate),120)
		Set @OneYear=CONVERT(varchar(10),dateadd(MONTH,-13,@CurrentDate),120)
		Set @TwoYear=CONVERT(varchar(10),dateadd(MONTH,-25,@CurrentDate),120)

		Select 1 N,z1.CompanyId,z1.name,Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
			Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
		 from (Select x.CompanyId,
				Case When AuditDate>@OneMonth then daishou else 0 end OneMonth,
				Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
				Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
				Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
				Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
				Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
				from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
						Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
						join Zzjg z1 on y.CompanyId=z1.CompanyId
		 where z1.CompanyId in ('+@CompanyId+')
		Group by z1.CompanyId,z1.name
		union
		Select 2,999,''总计'',Sum(OneMonth) OneMonth,Sum(OneToThreeMonth) OneToThreeMonth,Sum(ThreeToSixMonth) ThreeToSixMonth,
			Sum(SixMonthToOneYear) SixMonthToOneYear,Sum(OneYearToTwoYear) OneYearToTwoYear,Sum(TwoYearAbove) TwoYearAbove,Sum(Total) Total
		 from (Select x.CompanyId,
				Case When AuditDate>@OneMonth and AuditDate<=@CurrentDate then daishou else 0 end OneMonth,
				Case When AuditDate>@ThreeMonth and AuditDate<=@OneMonth then daishou else 0 end OneToThreeMonth,
				Case When AuditDate>@SixMonth and AuditDate<=@ThreeMonth then daishou else 0 end ThreeToSixMonth,
				Case When AuditDate>@OneYear and AuditDate<=@SixMonth then daishou else 0 end SixMonthToOneYear,
				Case When AuditDate>@TwoYear and AuditDate<=@OneYear then daishou else 0 end OneYearToTwoYear,
				Case When AuditDate<=@TwoYear then daishou else 0 end TwoYearAbove,daishou Total
				from (Select CompanyId,YingShouPrice-ShiShouPrice daishou,CONVERT(varchar(10),AuditDate,120) AuditDate from [Order] 
						Where YingShouPrice-ShiShouPrice>0 And StateId>=1 And NOE_Flag<>2 And FinanceRenlingDate is not null) x) y 
						join Zzjg z1 on y.CompanyId=z1.CompanyId 
		 where z1.CompanyId in ('+@CompanyId+')  
		 Order by CompanyId'
	exec(@sql)
END
